package com.example.jdbcsamples.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.example.jdbcsamples.entity.Department;

/**
 * 
 * @author Bora SAYINER
 * @since Feb 1, 2015 1:38:25 PM
 * @version 1.0.0
 */
public class DbUtil {

	private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
	private static final String DB_USER_NAME = "ex";
	private static final String DB_PASSWORD = "ex";

	public static List<Department> getAllDepartments() {
		List<Department> departments = new ArrayList<Department>();
		try (Connection connection = createNewConnection()) {
			try (Statement statement = connection.createStatement()) {
				try (ResultSet resultSet = statement.executeQuery("SELECT * FROM departments")) {
					while (resultSet.next()) {
						Department department = new Department();
						department.setNumber(resultSet.getInt("department_number"));
						department.setName(resultSet.getString("department_name"));
						departments.add(department);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return departments;
	}

	public static Department findDepartmentById(int id) {
		Department department = null;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "SELECT * FROM departments WHERE department_number = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, id);
				try (ResultSet resultSet = statement.executeQuery()) {
					if (resultSet.next()) {
						department = new Department();
						department.setNumber(resultSet.getInt("department_number"));
						department.setName(resultSet.getString("department_name"));
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return department;
	}

	public static boolean insertDeparment(Department department) {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "INSERT INTO departments (department_number, department_name) VALUES (?,?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, department.getNumber());
				statement.setString(2, department.getName());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static boolean updateDeparment(Department department) {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "UPDATE departments SET department_name = ? WHERE department_number = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setString(1, department.getName());
				statement.setInt(2, department.getNumber());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static boolean deleteDeparment(Department department) {
		boolean result = false;
		try (Connection connection = createNewConnection()) {
			final String sqlCmd = "DELETE FROM departments WHERE department_number = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, department.getNumber());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	private static Connection createNewConnection() throws SQLException {
		return DriverManager.getConnection(DB_URL, DB_USER_NAME, DB_PASSWORD);
	}

}
